Connecting Python Applications to SQLite

Course: Python Training

Topic: Connecting Python Applications to SQLite

Course Link: Python Training Course

Demo Link: Register for a Course Demo

Website: Visit JustAcademy

1. Introduction to SQLite in Python

SQLite is a lightweight relational database management system that stores data in a local database file. Unlike database systems that require a separate database server, SQLite can operate directly within an application. It is commonly used for desktop applications, small web applications, prototypes, local storage, testing, and automation tools.

Python provides the built-in sqlite3 module for communicating with SQLite databases. Developers can create database tables, insert records, retrieve information, update existing data, delete records, and manage transactions using SQL statements executed from Python code.

The sqlite3 module is part of Python's standard library in standard CPython distributions that include SQLite support, so a separate package installation is usually unnecessary.

Why Connect Python to SQLite?

• To store application data permanently between program executions.
• To organize information into related tables.
• To retrieve records efficiently using SQL queries.
• To maintain data consistency through database transactions.
• To build applications without managing a separate database server.
• To develop prototypes that can later migrate to a larger database system if required.

2. Learning Objectives

After completing this topic, learners should be able to:

• Understand SQLite databases and the Python sqlite3 module.
• Create database connections and cursor objects.
• Create tables with appropriate columns and constraints.
• Insert, retrieve, update, and delete database records.
• Use parameterized SQL queries to prevent SQL injection.
• Manage transactions using commit and rollback.
• Handle database exceptions and close connections correctly.
• Use row factories, aggregate queries, and joins.
• Build a practical Python application backed by an SQLite database.

3. Understanding SQLite Database Concepts

3.1 What Is a Database?

A database is an organized collection of information that can be stored, retrieved, updated, and managed. In a relational database, information is organized into tables containing rows and columns.

For example, a student management application might store student information in a table named students.

Each row represents one student, and each column represents a particular attribute of that student.

3.2 What Is SQLite?

SQLite is an embedded relational database engine. Its database is usually stored in a file such as students.db or application.sqlite3. Python opens that file and sends SQL statements to the SQLite engine through the sqlite3 module.

SQLite is useful for local applications and moderate workloads. Applications with many simultaneous writers, distributed database requirements, or complex centralized administration may benefit from a client-server database such as PostgreSQL or MySQL.

3.3 Important Database Terms

4. Importing the sqlite3 Module

Before connecting to SQLite, import Python's sqlite3 module.

import sqlite3

print("SQLite module imported successfully.")

No pip installation is normally required. If importing sqlite3 fails, check whether the Python distribution was built with SQLite support.

5. Connecting to an SQLite Database

5.1 Creating a Database Connection

The sqlite3.connect() function opens a connection to a database. If the specified database file does not exist, SQLite normally creates it.

import sqlite3

connection = sqlite3.connect("students.db")

print("Connected to SQLite database.")

connection.close()

Explanation:

• import sqlite3 imports the database interface.
• sqlite3.connect("students.db") opens or creates the database file.
• The returned connection object allows the application to execute SQL statements and manage transactions.
• connection.close() releases the database connection.

The relative database path is resolved in relation to the application's current working directory, not necessarily the directory containing the Python source file.

5.2 Using an In-Memory Database

SQLite can create a temporary database stored in memory rather than a permanent database file.

import sqlite3

connection = sqlite3.connect(":memory:")

connection.execute(
    "CREATE TABLE demo (id INTEGER PRIMARY KEY, name TEXT)"
)

connection.execute(
    "INSERT INTO demo (name) VALUES (?)",
    ("Python",)
)

connection.commit()

result = connection.execute(
    "SELECT id, name FROM demo"
)

print(result.fetchall())

connection.close()

Example output:

[(1, 'Python')]

An in-memory database is useful for automated tests and temporary calculations. Its contents normally disappear when its connection closes.

5.3 Using pathlib for a Reliable Database Location

For applications, it is often useful to construct a database path explicitly rather than relying on whichever directory happens to be the current working directory.

import sqlite3
from pathlib import Path

application_directory = Path(__file__).resolve().parent
database_directory = application_directory / "data"
database_directory.mkdir(parents=True, exist_ok=True)

database_path = database_directory / "students.db"

connection = sqlite3.connect(database_path)

print("Database location:", database_path)

connection.close()

This example places the database in a data directory alongside the script. Choose a suitable writable directory for the application and consider how database files will be backed up and deployed.

6. Understanding Connections and Cursors

6.1 Connection Object

A connection represents the active database session. It provides methods to execute SQL, commit changes, roll back transactions, and close the connection.

Common connection methods include:

• execute() — executes a single SQL statement.
• executemany() — executes a parameterized statement for multiple parameter sets.
• commit() — commits pending changes.
• rollback() — rolls back pending changes.
• cursor() — creates a cursor object.
• close() — closes the connection.

6.2 Cursor Object

A cursor executes SQL statements and exposes query results. The connection itself also provides shortcut methods such as connection.execute(), so creating a separate cursor is optional for many straightforward queries.

import sqlite3

connection = sqlite3.connect(":memory:")

cursor = connection.cursor()

cursor.execute(
    "CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT)"
)

cursor.execute(
    "INSERT INTO products (name) VALUES (?)",
    ("Keyboard",)
)

connection.commit()

cursor.execute("SELECT id, name FROM products")

print(cursor.fetchall())

cursor.close()
connection.close()

Example output:

[(1, 'Keyboard')]

7. Creating Database Tables

The SQL CREATE TABLE statement defines a table's columns and constraints.

import sqlite3

connection = sqlite3.connect("students.db")

connection.execute("""
    CREATE TABLE IF NOT EXISTS students (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT UNIQUE,
        age INTEGER,
        course TEXT NOT NULL
    )
""")

connection.commit()
connection.close()

print("Students table is ready.")

7.1 Understanding Column Definitions

SQLite uses flexible typing and type affinity. Declared column types influence storage and conversion behavior, but they do not work exactly like rigid type systems in some other relational databases.

7.2 Adding Constraints

CREATE TABLE IF NOT EXISTS employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    salary REAL CHECK (salary >= 0),
    active INTEGER NOT NULL DEFAULT 1
);

Constraints protect data quality by enforcing rules in the database itself. They are valuable even when the Python application also validates user input.

8. Inserting Data into SQLite

8.1 Inserting a Single Record

The INSERT INTO statement adds a new row. In Python, pass the values separately from the SQL statement using placeholders.

import sqlite3

connection = sqlite3.connect("students.db")

connection.execute("""
    INSERT INTO students (name, email, age, course)
    VALUES (?, ?, ?, ?)
""", ("Aarav", "[email protected]", 21, "Python"))

connection.commit()
connection.close()

print("Student inserted successfully.")

The question marks are parameter placeholders. Python's SQLite driver binds the supplied values safely instead of constructing SQL by joining strings.

8.2 Inserting Multiple Records with executemany()

import sqlite3

students = [
    ("Meera", "[email protected]", 22, "SQL"),
    ("Rohan", "[email protected]", 23, "Python"),
    ("Anaya", "[email protected]", 20, "Data Analytics")
]

connection = sqlite3.connect("students.db")

connection.executemany("""
    INSERT INTO students (name, email, age, course)
    VALUES (?, ?, ?, ?)
""", students)

connection.commit()
connection.close()

print("Multiple students inserted.")

executemany() is convenient for inserting multiple records using the same SQL statement. The example assumes that the email addresses do not already violate the table's unique constraint.

8.3 Using Named Parameters

Named placeholders can make queries easier to read when many fields are involved.

import sqlite3

student = {
    "name": "Ishaan",
    "email": "[email protected]",
    "age": 24,
    "course": "Python"
}

connection = sqlite3.connect("students.db")

connection.execute("""
    INSERT INTO students (name, email, age, course)
    VALUES (:name, :email, :age, :course)
""", student)

connection.commit()
connection.close()

The placeholder names must match the keys provided in the parameter mapping.

9. Retrieving Data with SELECT

9.1 Selecting All Records

import sqlite3

connection = sqlite3.connect("students.db")

cursor = connection.execute(
    "SELECT id, name, email, course FROM students"
)

rows = cursor.fetchall()

for row in rows:
    print(row)

connection.close()

fetchall() returns all remaining rows from the query as a list. Each row is normally a tuple unless a different row factory is configured.

9.2 Retrieving a Single Record

import sqlite3

connection = sqlite3.connect("students.db")

cursor = connection.execute(
    "SELECT id, name, course FROM students WHERE id = ?",
    (1,)
)

student = cursor.fetchone()

if student is not None:
    print(student)
else:
    print("Student not found.")

connection.close()

Important: A one-element tuple must include a trailing comma, as in (1,).

9.3 Filtering Records with WHERE

import sqlite3

connection = sqlite3.connect("students.db")

course_name = "Python"

rows = connection.execute("""
    SELECT id, name, age
    FROM students
    WHERE course = ?
    ORDER BY name
""", (course_name,)).fetchall()

for row in rows:
    print(row)

connection.close()

The WHERE clause filters the results, while ORDER BY determines the sorting order.

9.4 Using LIKE for Pattern Matching

import sqlite3

connection = sqlite3.connect("students.db")

pattern = "%an%"

rows = connection.execute("""
    SELECT name
    FROM students
    WHERE name LIKE ?
""", (pattern,)).fetchall()

for row in rows:
    print(row[0])

connection.close()

The percent sign in SQL LIKE patterns matches zero or more characters. In this example, the query looks for names containing the text an.

9.5 Using LIMIT

import sqlite3

connection = sqlite3.connect("students.db")

rows = connection.execute("""
    SELECT id, name
    FROM students
    ORDER BY id DESC
    LIMIT ?
""", (5,)).fetchall()

print(rows)

connection.close()

LIMIT restricts the number of returned rows. It is useful for previews and pagination when combined with an appropriate ordering strategy.

10. Preventing SQL Injection

SQL injection occurs when untrusted input is combined with SQL text in a way that changes the intended query. Python developers should use parameterized queries instead of formatting SQL statements with user-supplied values.

10.1 Unsafe Approach

# Do not use this pattern with untrusted input.
username = input("Enter username: ")

query = (
    "SELECT * FROM users WHERE username = '"
    + username
    + "'"
)

# Executing dynamically concatenated SQL is unsafe.

Joining user input directly into SQL can allow the input to alter the query's meaning.

10.2 Safe Parameterized Approach

import sqlite3

username = input("Enter username: ")

connection = sqlite3.connect("application.db")

user = connection.execute("""
    SELECT id, username
    FROM users
    WHERE username = ?
""", (username,)).fetchone()

print(user)

connection.close()

With a parameterized query, the value is bound as data rather than being interpreted as part of the SQL statement.

Remember: Parameters represent values, not SQL identifiers. If a user needs to choose a table or column name, select from an explicit allowlist of permitted identifiers instead of inserting arbitrary input into the SQL string.

11. Updating Existing Records

The SQL UPDATE statement modifies existing records. A WHERE clause should normally be included to restrict which records are changed.

import sqlite3

connection = sqlite3.connect("students.db")

connection.execute("""
    UPDATE students
    SET course = ?
    WHERE id = ?
""", ("Data Analytics", 1))

connection.commit()
connection.close()

print("Student record updated.")

Without a WHERE clause, an update can modify every row in the table.

12. Deleting Records

The SQL DELETE statement removes rows that match a condition.

import sqlite3

connection = sqlite3.connect("students.db")

connection.execute(
    "DELETE FROM students WHERE id = ?",
    (3,)
)

connection.commit()
connection.close()

print("Student record deleted.")

Always verify the condition before executing a destructive query. A DELETE statement without a WHERE clause deletes all rows in the table.

13. Managing Transactions

A transaction groups database operations into a logical unit. Transactions help preserve consistency when an application performs several related changes.

13.1 Committing Changes

In common transaction configurations, data-changing statements must be committed for their changes to be saved.

import sqlite3

connection = sqlite3.connect("students.db")

connection.execute(
    "INSERT INTO students (name, email, age, course) VALUES (?, ?, ?, ?)",
    ("Kabir", "[email protected]", 25, "SQL")
)

connection.commit()
connection.close()

13.2 Rolling Back Changes

rollback() cancels pending changes in the current transaction.

import sqlite3

connection = sqlite3.connect("students.db")

try:
    connection.execute(
        "INSERT INTO students (name, email, age, course) VALUES (?, ?, ?, ?)",
        ("Nisha", "[email protected]", 22, "Python")
    )

    # Cancel pending transaction changes.
    connection.rollback()

except sqlite3.Error as error:
    connection.rollback()
    print("Database error:", error)

finally:
    connection.close()

Because the transaction is rolled back, the inserted record is not retained by this transaction.

13.3 Using a Connection Context Manager

A connection can be used as a context manager to commit a successful transaction or roll it back when an exception occurs.

import sqlite3

connection = sqlite3.connect("students.db")

try:
    with connection:
        connection.execute(
            "UPDATE students SET course = ? WHERE id = ?",
            ("Python", 2)
        )

        connection.execute(
            "UPDATE students SET course = ? WHERE id = ?",
            ("SQL", 999999)
        )

except sqlite3.Error as error:
    print("Transaction failed:", error)

finally:
    connection.close()

The connection context manager manages transaction completion, but it does not automatically close the connection. Use close() or an appropriate closing pattern when finished.

14. Handling Database Exceptions

Database operations may fail because of invalid SQL, duplicate unique values, missing tables, locked databases, or filesystem permission problems. Python's sqlite3 module provides a family of exceptions for handling these conditions.

import sqlite3

connection = None

try:
    connection = sqlite3.connect("students.db")

    connection.execute(
        "INSERT INTO students (name, email, age, course) VALUES (?, ?, ?, ?)",
        ("Aarav", "[email protected]", 21, "Python")
    )

    connection.commit()
    print("Record saved successfully.")

except sqlite3.IntegrityError as error:
    if connection is not None:
        connection.rollback()
    print("Constraint violation:", error)

except sqlite3.OperationalError as error:
    if connection is not None:
        connection.rollback()
    print("Database operation failed:", error)

except sqlite3.Error as error:
    if connection is not None:
        connection.rollback()
    print("SQLite error:", error)

finally:
    if connection is not None:
        connection.close()

In this example, inserting a duplicate email can raise an integrity error because the table defines the email column as unique. For a production application, handle expected duplicate records in a user-friendly manner.

Common SQLite Exceptions

15. Using Row Factories for Readable Results

By default, SQLite query results are returned as tuples. Setting row_factory to sqlite3.Row allows access by column name as well as index.

import sqlite3

connection = sqlite3.connect("students.db")
connection.row_factory = sqlite3.Row

rows = connection.execute("""
    SELECT id, name, course
    FROM students
""").fetchall()

for row in rows:
    print("ID:", row["id"])
    print("Name:", row["name"])
    print("Course:", row["course"])

connection.close()

This approach can make database-backed application code easier to read because fields are accessed using meaningful names.

16. Using Aggregate Functions

SQLite supports common SQL aggregate functions, including COUNT(), SUM(), AVG(), MIN(), and MAX().

import sqlite3

connection = sqlite3.connect("students.db")

total_students = connection.execute(
    "SELECT COUNT(*) FROM students"
).fetchone()[0]

print("Total students:", total_students)

course_counts = connection.execute("""
    SELECT course, COUNT(*) AS total
    FROM students
    GROUP BY course
    ORDER BY total DESC
""").fetchall()

for course, total in course_counts:
    print(course, total)

connection.close()

GROUP BY combines rows with the same course value, allowing the application to count students in each course.

17. Connecting Related Tables with JOIN

Relational databases often store different types of information in separate tables. A join combines related records using a common key.

import sqlite3

connection = sqlite3.connect("school.db")

connection.executescript("""
    CREATE TABLE IF NOT EXISTS courses (
        id INTEGER PRIMARY KEY,
        title TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS enrollments (
        id INTEGER PRIMARY KEY,
        student_name TEXT NOT NULL,
        course_id INTEGER NOT NULL,
        FOREIGN KEY (course_id) REFERENCES courses(id)
    );
""")

connection.execute(
    "INSERT INTO courses (title) VALUES (?)",
    ("Python",)
)

connection.execute(
    "INSERT INTO enrollments (student_name, course_id) VALUES (?, ?)",
    ("Meera", 1)
)

connection.commit()

rows = connection.execute("""
    SELECT enrollments.student_name, courses.title
    FROM enrollments
    JOIN courses
        ON enrollments.course_id = courses.id
""").fetchall()

for row in rows:
    print(row)

connection.close()

Example output:

('Meera', 'Python')

Important: SQLite foreign-key enforcement should be enabled for each connection when the application relies on foreign-key constraints. Execute PRAGMA foreign_keys = ON immediately after connecting and before starting a transaction.

18. Using SQLite with Python Functions

Separating database operations into functions improves code reuse, testing, and maintenance. Instead of placing all SQL statements in one large script, create functions for specific tasks.

import sqlite3


def create_database(database_path):
    with sqlite3.connect(database_path) as connection:
        connection.execute("""
            CREATE TABLE IF NOT EXISTS tasks (
                id INTEGER PRIMARY KEY,
                title TEXT NOT NULL,
                completed INTEGER NOT NULL DEFAULT 0
            )
        """)


def add_task(database_path, title):
    with sqlite3.connect(database_path) as connection:
        cursor = connection.execute(
            "INSERT INTO tasks (title) VALUES (?)",
            (title,)
        )
        return cursor.lastrowid


def get_tasks(database_path):
    with sqlite3.connect(database_path) as connection:
        return connection.execute("""
            SELECT id, title, completed
            FROM tasks
            ORDER BY id
        """).fetchall()


database = "tasks.db"
create_database(database)

task_id = add_task(database, "Learn SQLite")
print("Created task:", task_id)

for task in get_tasks(database):
    print(task)

The connection context manager handles transaction completion. In this example, each function opens its own connection, which can be a simple and practical approach for a small application.

19. Practical Project: Student Management Application

This project demonstrates how to connect a Python application to SQLite and perform basic student-management operations. The application creates a database table, adds students, lists records, searches by ID, updates course information, and deletes records.

19.1 Project Structure

student_app/
    main.py
    students.db

The database file will be created when the application first connects to it.

19.2 Complete Project Code

import sqlite3
from pathlib import Path

BASE_DIR = Path(__file__).resolve().parent
DATABASE_PATH = BASE_DIR / "students.db"


def connect_database():
    connection = sqlite3.connect(DATABASE_PATH)
    connection.row_factory = sqlite3.Row
    return connection


def create_table():
    with connect_database() as connection:
        connection.execute("""
            CREATE TABLE IF NOT EXISTS students (
                id INTEGER PRIMARY KEY,
                name TEXT NOT NULL,
                email TEXT NOT NULL UNIQUE,
                course TEXT NOT NULL
            )
        """)


def add_student(name, email, course):
    with connect_database() as connection:
        cursor = connection.execute("""
            INSERT INTO students (name, email, course)
            VALUES (?, ?, ?)
        """, (name, email, course))

        return cursor.lastrowid


def list_students():
    with connect_database() as connection:
        rows = connection.execute("""
            SELECT id, name, email, course
            FROM students
            ORDER BY id
        """).fetchall()

        return [dict(row) for row in rows]


def find_student(student_id):
    with connect_database() as connection:
        row = connection.execute("""
            SELECT id, name, email, course
            FROM students
            WHERE id = ?
        """, (student_id,)).fetchone()

        return dict(row) if row is not None else None


def update_course(student_id, course):
    with connect_database() as connection:
        cursor = connection.execute("""
            UPDATE students
            SET course = ?
            WHERE id = ?
        """, (course, student_id))

        return cursor.rowcount > 0


def delete_student(student_id):
    with connect_database() as connection:
        cursor = connection.execute(
            "DELETE FROM students WHERE id = ?",
            (student_id,)
        )

        return cursor.rowcount > 0


def main():
    create_table()

    try:
        add_student(
            "Aarav",
            "[email protected]",
            "Python"
        )
    except sqlite3.IntegrityError:
        print("Student already exists; skipping duplicate.")

    print("All students:")
    for student in list_students():
        print(student)

    student = find_student(1)
    print("Search result:", student)

    if update_course(1, "Data Analytics"):
        print("Course updated.")

    if delete_student(999):
        print("Student deleted.")
    else:
        print("No student found with ID 999.")


if __name__ == "__main__":
    main()

19.3 Running the Application

1. Install Python if it is not already available.
2. Create a folder named student_app.
3. Save the code as main.py inside that folder.
4. Open a terminal in the folder.
5. Run python main.py.
6. Run it again to see how the unique email constraint prevents duplicate records.

The project uses a database file stored beside the Python script, named queries, parameter binding, reusable functions, and a row factory. In a real application, add user input validation, logging, tests, and a deliberate strategy for database migrations.

20. Testing SQLite Applications

Database code should be tested to verify that data is inserted correctly, queries return expected results, and errors are handled predictably.

20.1 Using an In-Memory Database for Tests

import sqlite3
import unittest


class StudentDatabaseTests(unittest.TestCase):
    def setUp(self):
        self.connection = sqlite3.connect(":memory:")

        self.connection.execute("""
            CREATE TABLE students (
                id INTEGER PRIMARY KEY,
                name TEXT NOT NULL
            )
        """)

    def test_insert_student(self):
        self.connection.execute(
            "INSERT INTO students (name) VALUES (?)",
            ("Meera",)
        )

        row = self.connection.execute(
            "SELECT name FROM students WHERE id = ?",
            (1,)
        ).fetchone()

        self.assertEqual(row[0], "Meera")

    def tearDown(self):
        self.connection.close()


if __name__ == "__main__":
    unittest.main()

An in-memory database makes tests isolated and avoids changing a real application database during routine test runs.

21. Performance and Reliability Best Practices

• Use parameterized queries: Keep data values separate from SQL statements.
• Commit deliberately: Group related writes into transactions rather than committing unnecessarily after every small operation.
• Close connections: Release database resources when they are no longer needed.
• Use context managers: Make transaction behavior clearer and reduce the risk of leaving changes uncommitted.
• Create indexes where useful: Index columns that are frequently used for searching, joining, or sorting, while considering the cost of additional indexes on writes.
• Keep queries focused: Select only the columns and rows needed for the task.
• Validate input: Check data before inserting or updating records.
• Use appropriate constraints: Apply primary keys, unique constraints, and foreign keys where required.
• Handle lock errors: Keep write transactions short and account for contention between connections.
• Back up the database: Establish a reliable backup and recovery process for important application data.
• Use migrations: Manage schema changes in a repeatable way as the application evolves.
• Test failure scenarios: Test duplicate records, missing data, invalid values, and transaction rollbacks.

22. Common Errors and Troubleshooting

23. Interview Questions and Answers

Q1. What is SQLite?

Answer: SQLite is an embedded relational database engine that stores data in a local file and does not require a separate database server process.

Q2. Which Python module is used to connect to SQLite?

Answer: Python uses the standard-library sqlite3 module.

Q3. What does sqlite3.connect() do?

Answer: It opens a connection to an SQLite database. If a file-backed database does not exist, it is normally created.

Q4. What is the difference between a connection and a cursor?

Answer: A connection represents the database session and manages transactions. A cursor executes SQL statements and provides access to query results. Connection shortcut methods can execute many statements without an explicit cursor.

Q5. What is the purpose of commit()?

Answer: It commits pending transaction changes so that they are saved according to the transaction mode and database behavior.

Q6. What does rollback() do?

Answer: It cancels pending changes in the current transaction.

Q7. Why should parameterized queries be used?

Answer: They separate data from SQL syntax and help prevent SQL injection when handling untrusted values.

Q8. What is the difference between fetchone() and fetchall()?

Answer: fetchone() retrieves the next row or returns None when no row remains. fetchall() retrieves all remaining rows.

Q9. What does executemany() do?

Answer: It executes a parameterized SQL statement for multiple sets of values.

Q10. How can duplicate records be prevented?

Answer: Define suitable database constraints, such as UNIQUE, and handle sqlite3.IntegrityError appropriately.

Q11. What is an in-memory SQLite database?

Answer: It is a database created using sqlite3.connect(":memory:") whose data normally exists only for the lifetime of the connection.

Q12. How can Python results be accessed by column name?

Answer: Set the connection's row_factory to sqlite3.Row and access values using column names.

Q13. What is a database transaction?

Answer: A transaction groups related operations so that they can be committed together or rolled back if an error occurs.

Q14. How can SQLite tables be related?

Answer: Use primary keys and foreign keys, then retrieve related data using SQL joins. Enable foreign-key enforcement on each connection when required.

Q15. When might SQLite not be the best choice?

Answer: Applications that require many concurrent writers, centralized access across many application servers, or advanced database administration may be better suited to a client-server database.

24. Practice Exercises

1. Create a database named library.db and a table named books.
2. Insert five books using executemany().
3. Retrieve all books published after a specified year.
4. Update the price of a book using a parameterized query.
5. Delete a book by its primary key.
6. Create a unique constraint for book ISBN values.
7. Use sqlite3.Row to access book details by column name.
8. Write a function that searches for books by title.
9. Wrap multiple related inserts in a transaction and test rollback behavior.
10. Create a second table for authors and use a join to display books with their authors.

25. Quick Revision Checklist

• sqlite3.connect() opens or creates a database.
• connection.execute() executes an SQL statement.
• connection.cursor() creates a cursor.
• CREATE TABLE defines a table structure.
• INSERT adds records.
• SELECT retrieves records.
• UPDATE modifies records.
• DELETE removes records.
• ? placeholders bind values safely in SQL statements.
• fetchone() retrieves one row; fetchall() retrieves remaining rows.
• commit() and rollback() control transaction outcomes.
• sqlite3.Row supports column-name access.
• Use constraints to protect data integrity.
• Handle SQLite exceptions and close connections.
• Use backups, testing, and schema migrations for reliable applications.

26. Additional Learning Resources

• Official Python Documentation: sqlite3 Module
• Official SQLite Documentation
• Python Database API Specification (PEP 249)
• Explore Python Training at JustAcademy
• Register for a Course Demo
• Visit the JustAcademy Homepage

27. Conclusion

Connecting Python applications to SQLite enables developers to build data-driven programs without setting up a separate database server. The sqlite3 module provides the core tools needed to connect to a database, create tables, insert and retrieve records, update information, delete data, and manage transactions.

Reliable database applications use parameterized queries, suitable constraints, deliberate transaction management, exception handling, and proper connection cleanup. Practicing these concepts through a student management application or library database provides a strong foundation for more advanced Python database development.

Final Takeaway: Learn to connect, query, modify, and protect SQLite data from Python code. These skills are useful for automation scripts, desktop applications, prototypes, testing, and many other practical software projects.